db size and tables size difference

Hi Pg Admin list.
I'm trying to understand why there are GB's difference when checking
for db size using pg_size_pretty() and querying for tables + indexes
size. .
The sum of tables +index sizes is showing as aprox 6.5GB and
pg_size_pretty(dbname) is coming as 12GB, this are the results after a
full vacuum and reindexdb, also the sum of OS db files size is ~ 6.5GB.
Any tips what I'm missing : are some "unallocated" db pages or anything
else ?

select pg_size_pretty(pg_database_size('db1'));
pg_size_pretty
----------------
12 GB
(1 row)
*** Check for tables size :
SELECT schemaname, tablename,
pg_size_pretty(size) AS size_pretty,
pg_size_pretty(total_size) AS total_size_pretty
FROM (SELECT *,
pg_relation_size(schemaname||'.'||tablename) AS
size,pg_total_relation_size(schemaname||'.'||tablename) AS
total_size FROM pg_tables) AS TABLES where schemaname='caom'
ORDER BY total_size DESC;
schemaname | tablename | size_pretty | total_size_pretty
------------+-----------------------------+-------------+--- ----------------
caom | spatialentity | 3053 MB | 3216 MB
caom | artifact | 1979 MB | 2150 MB
caom | plane | 413 MB | 677 MB
caom | positionsample | 110 MB | 219 MB
caom | simpleobservation | 165 MB | 202 MB
caom | temporalentity | 77 MB | 86 MB
caom | spectralentity | 68 MB | 73 MB
caom | metric | 33 MB | 70 MB
caom | polarizationentity | 29 MB | 33 MB
caom | harvestskip | 1576 kB | 2056 kB
caom | harveststate | 840 kB | 856 kB
caom | positionhole | 48 kB | 584 kB




*** OR : check for the biggest tables+index size:
SELECT ' Top 20 biggest tables and indexes'
;
SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_relation_size(nspname || '.' || relname)) AS "size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND nspname !~ '^pg_toast'
AND pg_relation_size(nspname || '.' || relname)>0
ORDER BY pg_relation_size(nspname || '.' || relname) DESC
LIMIT 20;
(1 row)

relation | size
---------------------------+---------
caom.spatialentity | 3053 MB
caom.artifact | 1979 MB
caom.plane | 413 MB
caom.artifact_i1 | 171 MB
caom.simpleobservation | 165 MB
caom.spatialentity_i1 | 162 MB
caom.positionsample | 110 MB
caom.plane_psi2 | 86 MB
caom.temporalentity | 77 MB
caom.spectralentity | 68 MB
caom.plane_energy_i1 | 67 MB
caom.plane_time_i1 | 58 MB
caom.plane_position_i2 | 48 MB
caom.metric | 33 MB
caom.polarizationentity | 29 MB
caom.simpleobservation_i2 | 25 MB
caom.plane_psi1 | 23 MB
caom.metric_i2 | 18 MB

Thank you
Isabella

--
-----------------------------------------------------------
Isabella A. Ghiurea

Isabella.Ghiurea@nrc-cnrc.gc.ca
Canadian Astronomy Data Centre | http://www1.cadc-ccda.hia-iha.nrc-cnrc.gc.ca/
National Research Council of Canada, Herzberg Institute of Astrophysics
5071 West Saanich Road, Victoria BC V9E 2E7, Canada
Phone: 250 363-3446 fax: 250 363-0045


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: db size and tables size difference

Isabella Ghiurea writes:
> I'm trying to understand why there are GB's difference when checking
> for db size using pg_size_pretty() and querying for tables + indexes
> size. .

You are not counting everything --- the total DB size is clearly 12GB,
so the question is where are the other 5.5GB? Your first query shows
that schema caom accounts for 6+GB, but the second one does not prove
that schema caom contains all the big hogs. My guesses are:

1. Toast tables for tables that aren't in caom --- you used
pg_relation_size not pg_total_relation_size, and excluded toast
tables, so you are missing those.

2. pg_largeobject ... got any large objects?

3. Bloat in other system catalogs. 5GB of catalog bloat would be
pretty awful, but maybe that's what it is.

Try that last query without the namespace restrictions.

regards, tom lane

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: db size and tables size difference

How would one check for catalog bloat? And, if bloated, would unloading
and reloading the database
fix it?

Naomi
>
> 3. Bloat in other system catalogs. 5GB of catalog bloat would be
> pretty awful, but maybe that's what it is.
>
> Try that last query without the namespace restrictions.
>
> regards, tom lane
>
>


--
------------------------------------------------------------ ------------
Naomi Walker Chief Information Officer
Eldorado Computing, Inc nwalker@eldocomp.com
---An EDS Company 602-604-3100
------------------------------------------------------------ ------------
Hard things are put in our way, not to stop us, but to call out our
courage and strength. -- Anonymous
------------------------------------------------------------ ------------


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: db size and tables size difference

Naomi Walker writes:
> How would one check for catalog bloat?

Like I said:

>> Try that last query without the namespace restrictions.

Isabella excluded pg_catalog, so if that's where the problem is,
that's why she didn't see it. But let's see the data before
discussing how to fix it...

regards, tom lane

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: db size and tables size difference

Hi All,

Please, see more info my env: PG 8.3.6 on RHE5-64bits.

1. there are more than one schemas, but the size of the tables is close
to 30-40kB, see some samples
schemaname | tablename | size_pretty | total_size_pretty
------------+-------------+-------------+-------------------
tap_schema | tables | 8192 bytes | 32 kB
tap_schema | columns | 8192 bytes | 32 kB
tap_schema | schemas | 8192 bytes | 32 kB
tap_schema | keys | 8192 bytes | 24 kB
tap_schema | key_columns | 8192 bytes | 8192 bytes

2. There are no BLOB's data type in db at this time :
cvodb=# select * from pg_largeobject;
loid | pageno | data

------+--------+------
(0 rows)


3. As Tom suggested , I excluded the table space restriction and
changed to pg_total_relation_size my original SQL :


SELECT 'the table size without table space restrictions';
SELECT nspname || '.' || relname AS
"relation",pg_size_pretty(pg_total_relation_size(nspname || '.' ||
relname)) AS "s
ize"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
ORDER BY pg_relation_size(nspname || '.' || relname) DESC
LIMIT 20;
the table size without table space restrictions
(1 row)

relation | size
---------------------------+---------
caom.spatialentity | 3216 MB
caom.artifact | 2150 MB
caom.plane | 677 MB
caom.artifact_i1 | 171 MB
caom.simpleobservation | 202 MB
caom.spatialentity_i1 | 162 MB
caom.positionsample | 219 MB
caom.plane_psi2 | 86 MB
caom.temporalentity | 86 MB
caom.spectralentity | 73 MB
caom.plane_energy_i1 | 67 MB
caom.plane_time_i1 | 58 MB
caom.plane_position_i2 | 48 MB
caom.metric | 70 MB
caom.polarizationentity | 33 MB
caom.simpleobservation_i2 | 25 MB
caom.plane_psi1 | 23 MB
caom.metric_i2 | 18 MB
caom.metric_i1 | 18 MB
caom.plane_position_i3 | 15 MB
(20 rows)


4. Where are the rest of 5,5 GB been used ? How can I get the system
catalog correct size ?



Thank you,
Isabella

Tom Lane wrote:
>
> Isabella Ghiurea writes:
> > I'm trying to understand why there are GB's difference when checking
> > for db size using pg_size_pretty() and querying for tables + indexes
> > size. .
>
> You are not counting everything --- the total DB size is clearly 12GB,
> so the question is where are the other 5.5GB? Your first query shows
> that schema caom accounts for 6+GB, but the second one does not prove
> that schema caom contains all the big hogs. My guesses are:
>
> 1. Toast tables for tables that aren't in caom --- you used
> pg_relation_size not pg_total_relation_size, and excluded toast
> tables, so you are missing those.
>
> 2. pg_largeobject ... got any large objects?
>
> 3. Bloat in other system catalogs. 5GB of catalog bloat would be
> pretty awful, but maybe that's what it is.
>
> Try that last query without the namespace restrictions.
>


>
> regards, tom lane
>


--
-----------------------------------------------------------
Isabella A. Ghiurea

Isabella.Ghiurea@nrc-cnrc.gc.ca
Canadian Astronomy Data Centre | http://www1.cadc-ccda.hia-iha.nrc-cnrc.gc.ca/
National Research Council of Canada, Herzberg Institute of Astrophysics
5071 West Saanich Road, Victoria BC V9E 2E7, Canada
Phone: 250 363-3446 fax: 250 363-0045


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: db size and tables size difference

Isabella Ghiurea writes:
> SELECT nspname || '.' || relname AS
> "relation",pg_size_pretty(pg_total_relation_size(nspname || '.' ||
> relname)) AS "s
> ize"
> FROM pg_class C
> LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
> ORDER BY pg_relation_size(nspname || '.' || relname) DESC
> LIMIT 20;

I think maybe you'd better ORDER BY pg_total_relation_size instead.
Also, maybe look further than 20 rows ... maybe the issue is many
thousands of little tables?

regards, tom lane

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: db size and tables size difference

Hi All,
Tom, please see bellow are the results for the modified query with ORDER
BY

select pg_size_pretty(pg_database_size('db1')); ---->
pg_size_pretty
----------------
12 GB

SELECT 'the table size without table space restrictions';
SELECT nspname || '.' || relname AS
"relation",pg_size_pretty(pg_total_relation_size(nspname || '.' || relname))
AS "s
ize"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
ORDER BY pg_total_relation_size(nspname || '.' || relname) DESC
LIMIT 1000;

------------------------------------------------------+----- -------
caom.spatialentity | 3216 MB
caom.artifact | 2150 MB
caom.plane | 677 MB
caom.positionsample | 219 MB
caom.simpleobservation | 202 MB
caom.artifact_i1 | 171 MB
caom.spatialentity_i1 | 162 MB
caom.temporalentity | 86 MB
caom.plane_psi2 | 86 MB
caom.spectralentity | 73 MB
caom.metric | 70 MB
caom.plane_energy_i1 | 67 MB
caom.plane_time_i1 | 58 MB
caom.plane_position_i2 | 48 MB
caom.polarizationentity | 33 MB
caom.simpleobservation_i2 | 25 MB
caom.plane_psi1 | 23 MB
caom.metric_i2 | 18 MB
caom.metric_i1 | 18 MB
caom.plane_i1 | 15 MB
caom.plane_position_i3 | 15 MB
caom.plane_polar_i1 | 15 MB
caom.plane_time_i2 | 15 MB
caom.plane_energy_i2 | 15 MB
caom.plane_i2 | 15 MB
caom.simpleobservation_i1 | 12 MB
caom.temporalentity_i1 | 9496 kB
caom.spectralentity_i1 | 4384 kB
caom.polarizationentity_i1 | 4368 kB
caom.harvestskip | 2056 kB
pg_catalog.pg_depend | 1008 kB
pg_catalog.pg_proc | 880 kB
caom.harveststate | 856 kB
pg_catalog.pg_attribute | 648 kB
caom.positionhole | 584 kB
pg_catalog.pg_statistic | 576 kB
caom.plane_phi2 | 496 kB
caom.harvestskip_i1 | 480 kB
pg_catalog.pg_proc_proname_args_nsp_index | 328 kB
pg_catalog.pg_operator | 296 kB
pg_catalog.pg_description | 280 kB
pg_catalog.pg_depend_depender_index | 264 kB
pg_catalog.pg_depend_reference_index | 264 kB
pg_catalog.pg_rewrite | 256 kB
pg_catalog.pg_attribute_relid_attnam_index | 240 kB
pg_toast.pg_toast_2618 | 160 kB
pg_catalog.pg_type | 144 kB
pg_catalog.pg_class | 136 kB
pg_catalog.pg_amop | 128 kB
pg_catalog.pg_operator_oprname_l_r_n_index | 112 kB
pg_catalog.pg_description_o_c_o_index | 96 kB
pg_toast.pg_toast_2619 | 96 kB
pg_catalog.pg_constraint | 80 kB
pg_catalog.pg_conversion | 80 kB
pg_catalog.pg_amproc | 72 kB
pg_catalog.pg_attribute_relid_attnum_index | 72 kB
pg_catalog.pg_proc_oid_index | 72 kB
pg_catalog.pg_opclass | 72 kB
pg_catalog.pg_trigger | 56 kB
pg_catalog.pg_type_typname_nsp_index | 56 kB
pg_catalog.pg_index | 56 kB
information_schema.sql_features | 48 kB
pg_catalog.pg_cast | 48 kB
pg_catalog.pg_class_relname_nsp_index | 48 kB
pg_catalog.pg_database | 48 kB
pg_catalog.pg_authid | 48 kB
pg_catalog.pg_ts_config_map | 48 kB
pg_catalog.pg_opfamily | 48 kB
pg_catalog.pg_language | 40 kB
pg_catalog.pg_shdepend | 40 kB
pg_catalog.pg_ts_parser | 40 kB
pg_catalog.pg_ts_template | 40 kB
pg_catalog.pg_auth_members | 40 kB
pg_catalog.pg_ts_config | 40 kB
pg_catalog.pg_ts_dict | 40 kB
pg_catalog.pg_operator_oid_index | 40 kB
pg_catalog.pg_tablespace | 40 kB
pg_catalog.pg_namespace | 40 kB
pg_catalog.pg_am | 40 kB
pg_toast.pg_toast_45054 | 32 kB
pg_catalog.pg_conversion_name_nsp_index | 32 kB
pg_catalog.pg_shdescription | 32 kB
tap_schema.tables | 32 kB
pg_catalog.pg_statistic_relid_att_index | 32 kB
pg_catalog.pg_amop_fam_strat_index | 32 kB
tap_schema.schemas | 32 kB
pg_catalog.pg_amproc_fam_proc_index | 32 kB
pg_catalog.pg_ts_config_map_index | 32 kB
tap_schema.columns | 32 kB
caom.plane_phi1 | 32 kB
pg_catalog.pg_amop_oid_index | 32 kB
pg_catalog.pg_amop_opr_fam_index | 32 kB
pg_catalog.pg_opclass_am_name_nsp_index | 32 kB
caom.deletedplane | 24 kB
pg_catalog.pg_pltemplate | 24 kB
pg_catalog.pg_attrdef | 24 kB
tap_schema.keys | 24 kB
caom.deletedspatialentity | 24 kB
caom.deletedpolarizationentity | 24 kB
caom.compositeobservation | 24 kB
caom.deletedtemporalentity | 24 kB
caom.deletedartifact | 24 kB
caom.deletedspectralentity | 24 kB
pg_catalog.pg_aggregate | 24 kB
information_schema.sql_implementation_info | 16 kB
information_schema.sql_languages | 16 kB
pg_catalog.pg_enum | 16 kB
pg_catalog.pg_ts_config_oid_index | 16 kB
pg_catalog.pg_trigger_oid_index | 16 kB
tap_schema.schemas_pkey | 16 kB
pg_catalog.pg_constraint_conrelid_index | 16 kB
pg_catalog.pg_conversion_default_index | 16 kB
pg_catalog.pg_namespace_oid_index | 16 kB
pg_catalog.pg_namespace_nspname_index | 16 kB
pg_catalog.pg_cast_source_target_index | 16 kB
pg_catalog.pg_constraint_contypid_index | 16 kB
information_schema.sql_packages | 16 kB
pg_catalog.pg_aggregate_fnoid_index | 16 kB
pg_catalog.pg_rewrite_oid_index | 16 kB
pg_toast.pg_toast_2618_index | 16 kB
pg_catalog.pg_trigger_tgconstrname_index | 16 kB
pg_catalog.pg_trigger_tgrelid_tgname_index | 16 kB
information_schema.sql_parts | 16 kB
pg_catalog.pg_authid_rolname_index | 16 kB
pg_catalog.pg_authid_oid_index | 16 kB
pg_catalog.pg_auth_members_role_member_index | 16 kB
pg_catalog.pg_auth_members_member_role_index | 16 kB
information_schema.sql_sizing | 16 kB
tap_schema.keys_pkey | 16 kB
tap_schema.columns_pkey | 16 kB
caom.deletedplane_i1 | 16 kB
pg_catalog.pg_database_datname_index | 16 kB
pg_catalog.pg_database_oid_index | 16 kB
caom.deletedpolarizationentity_i1 | 16 kB
pg_catalog.pg_shdescription_o_c_index | 16 kB
pg_catalog.pg_language_name_index | 16 kB
pg_catalog.pg_language_oid_index | 16 kB
pg_catalog.pg_index_indrelid_index | 16 kB
caom.timesample | 16 kB
pg_catalog.pg_am_name_index | 16 kB
pg_toast.pg_toast_2619_index | 16 kB
pg_catalog.pg_constraint_conname_nsp_index | 16 kB
pg_catalog.pg_index_indexrelid_index | 16 kB
pg_catalog.pg_am_oid_index | 16 kB
pg_catalog.pg_pltemplate_name_index | 16 kB
pg_catalog.pg_shdepend_depender_index | 16 kB
pg_catalog.pg_shdepend_reference_index | 16 kB
pg_catalog.pg_tablespace_oid_index | 16 kB
pg_catalog.pg_tablespace_spcname_index | 16 kB
pg_toast.pg_toast_45054_index | 16 kB
caom.deletedtemporalentity_i1 | 16 kB
pg_catalog.pg_ts_dict_oid_index | 16 kB
caom.deletedspectralentity_i1 | 16 kB
caom.deletedspatialentity_i1 | 16 kB
caom.deletedartifact_i1 | 16 kB
pg_catalog.pg_constraint_oid_index | 16 kB
pg_catalog.pg_opfamily_am_name_nsp_index | 16 kB
pg_catalog.pg_opfamily_oid_index | 16 kB
pg_catalog.pg_opclass_oid_index | 16 kB
pg_catalog.pg_amproc_oid_index | 16 kB
pg_catalog.pg_class_oid_index | 16 kB
pg_catalog.pg_rewrite_rel_rulename_index | 16 kB
pg_catalog.pg_ts_config_cfgname_index | 16 kB
tap_schema.tables_pkey | 16 kB


Thank you
Isabella


Hi All,

Please, see more info my env: PG 8.3.6 on RHE5-64bits.

1. there are more than one schemas, but the size of the tables is close to
30-40kB, see some samples
schemaname | tablename | size_pretty | total_size_pretty
------------+-------------+-------------+-------------------
tap_schema | tables | 8192 bytes | 32 kB
tap_schema | columns | 8192 bytes | 32 kB
tap_schema | schemas | 8192 bytes | 32 kB
tap_schema | keys | 8192 bytes | 24 kB
tap_schema | key_columns | 8192 bytes | 8192 bytes

2. There are no BLOB's data type in db at this time :
cvodb=# select * from pg_largeobject;
loid | pageno | data

------+--------+------
(0 rows)


3. As Tom suggested , I excluded the table space restriction and changed
to pg_total_relation_size my original SQL :


SELECT 'the table size without table space restrictions';
SELECT nspname || '.' || relname AS
"relation",pg_size_pretty(pg_total_relation_size(nspname || '.' || relname))
AS "s
ize"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
ORDER BY pg_relation_size(nspname || '.' || relname) DESC
LIMIT 20;
the table size without table space restrictions
(1 row)

relation | size
---------------------------+---------
caom.spatialentity | 3216 MB
caom.artifact | 2150 MB
caom.plane | 677 MB
caom.artifact_i1 | 171 MB
caom.simpleobservation | 202 MB
caom.spatialentity_i1 | 162 MB
caom.positionsample | 219 MB
caom.plane_psi2 | 86 MB
caom.temporalentity | 86 MB
caom.spectralentity | 73 MB
caom.plane_energy_i1 | 67 MB
caom.plane_time_i1 | 58 MB
caom.plane_position_i2 | 48 MB
caom.metric | 70 MB
caom.polarizationentity | 33 MB
caom.simpleobservation_i2 | 25 MB
caom.plane_psi1 | 23 MB
caom.metric_i2 | 18 MB
caom.metric_i1 | 18 MB
caom.plane_position_i3 | 15 MB
(20 rows)


4. Where are the rest of 5,5 GB been used ? How can I get the system
catalog correct size ?



Thank you,
Isabella

Tom Lane wrote:
>
> Isabella Ghiurea writes:
> > I'm trying to understand why there are GB's difference when checking
> > for db size using pg_size_pretty() and querying for tables + indexes
> > size. .
>
> You are not counting everything --- the total DB size is clearly 12GB,
> so the question is where are the other 5.5GB? Your first query shows
> that schema caom accounts for 6+GB, but the second one does not prove
> that schema caom contains all the big hogs. My guesses are:
>
> 1. Toast tables for tables that aren't in caom --- you used
> pg_relation_size not pg_total_relation_size, and excluded toast
> tables, so you are missing those.
>
> 2. pg_largeobject ... got any large objects?
>
> 3. Bloat in other system catalogs. 5GB of catalog bloat would be
> pretty awful, but maybe that's what it is.
>
> Try that last query without the namespace restrictions.
>


>
> regards, tom lane

Hi Pg Admin list.
I'm trying to understand why there are GB's difference when checking for
db size using pg_size_pretty() and querying for tables + indexes size. .
The sum of tables +index sizes is showing as aprox 6.5GB and
pg_size_pretty(dbname) is coming as 12GB, this are the results after a full
vacuum and reindexdb, also the sum of OS db files size is ~ 6.5GB. Any tips
what I'm missing : are some "unallocated" db pages or anything else ?

select pg_size_pretty(pg_database_size('db1'));
pg_size_pretty
----------------
12 GB
(1 row)
*** Check for tables size :
SELECT schemaname, tablename,
pg_size_pretty(size) AS size_pretty,
pg_size_pretty(total_size) AS total_size_pretty
FROM (SELECT *,
pg_relation_size(schemaname||'.'||tablename) AS
size,pg_total_relation_size(schemaname||'.'||tablename) AS
total_size FROM pg_tables) AS TABLES where schemaname='caom'
ORDER BY total_size DESC;
schemaname | tablename | size_pretty | total_size_pretty
------------+-----------------------------+-------------+--- ----------------
caom | spatialentity | 3053 MB | 3216 MB
caom | artifact | 1979 MB | 2150 MB
caom | plane | 413 MB | 677 MB
caom | positionsample | 110 MB | 219 MB
caom | simpleobservation | 165 MB | 202 MB
caom | temporalentity | 77 MB | 86 MB
caom | spectralentity | 68 MB | 73 MB
caom | metric | 33 MB | 70 MB
caom | polarizationentity | 29 MB | 33 MB
caom | harvestskip | 1576 kB | 2056 kB
caom | harveststate | 840 kB | 856 kB
caom | positionhole | 48 kB | 584 kB




*** OR : check for the biggest tables+index size:
SELECT ' Top 20 biggest tables and indexes'
;
SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_relation_size(nspname || '.' || relname)) AS "size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND nspname !~ '^pg_toast'
AND pg_relation_size(nspname || '.' || relname)>0
ORDER BY pg_relation_size(nspname || '.' || relname) DESC
LIMIT 20;
(1 row)

relation | size
---------------------------+---------
caom.spatialentity | 3053 MB
caom.artifact | 1979 MB
caom.plane | 413 MB
caom.artifact_i1 | 171 MB
caom.simpleobservation | 165 MB
caom.spatialentity_i1 | 162 MB
caom.positionsample | 110 MB
caom.plane_psi2 | 86 MB
caom.temporalentity | 77 MB
caom.spectralentity | 68 MB
caom.plane_energy_i1 | 67 MB
caom.plane_time_i1 | 58 MB
caom.plane_position_i2 | 48 MB
caom.metric | 33 MB
caom.polarizationentity | 29 MB
caom.simpleobservation_i2 | 25 MB
caom.plane_psi1 | 23 MB
caom.metric_i2 | 18 MB

Thank you
Isabella


--
View this message in context: http://www.nabble.com/Re%3A--ADMIN--db-size-and-tables-size- difference-tp25531211p25531211.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: db size and tables size difference

Isabella Ghiurea writes:
> SELECT nspname || '.' || relname AS
> "relation",pg_size_pretty(pg_total_relation_size(nspname || '.' || relname))
> AS "s
> ize"
> FROM pg_class C
> LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
> ORDER BY pg_total_relation_size(nspname || '.' || relname) DESC
> LIMIT 1000;

Hmph ... I can't see anything wrong with that query, so it seems that
we're left with the conclusion that there are files in the database
directory that don't match any entry in the catalogs. AFAIK this'd
only be possible if you'd had a crash while deleting tables or some
similar problem. What you'll need to do next is poke around in the
data directory and see if you can identify any large files that do
not correspond to any entry in pg_class.relfilenode. You should
read the internals docs first, if you're not familiar with this
chapter:
http://www.postgresql.org/docs/8.3/static/storage.html

regards, tom lane

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: db size and tables size difference

Hi All,
looking in more details on OS partitions sizwe and each table space
corresponding to OS partitions will add up to close to 6,5GB for db size
same result as SQL table size.
The issue may be with pg_size_pretty() results, I don't have details
knowledge of this function.
select pg_size_pretty(pg_database_size('db1'));
pg_size_pretty
----------------
12 GB

Isabella


Isabella Ghiurea wrote:
>
> Hi All,
> Tom, please see bellow are the results for the modified query with
> ORDER BY
>
> select pg_size_pretty(pg_database_size('db1')); ---->
> pg_size_pretty
> ----------------
> 12 GB
>
> SELECT 'the table size without table space restrictions';
> SELECT nspname || '.' || relname AS
> "relation",pg_size_pretty(pg_total_relation_size(nspname || '.' ||
> relname)) AS "s
> ize"
> FROM pg_class C
> LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
> ORDER BY pg_total_relation_size(nspname || '.' || relname) DESC
> LIMIT 1000;
>
> ------------------------------------------------------+----- -------
> caom.spatialentity | 3216 MB
> caom.artifact | 2150 MB
> caom.plane | 677 MB
> caom.positionsample | 219 MB
> caom.simpleobservation | 202 MB
> caom.artifact_i1 | 171 MB
> caom.spatialentity_i1 | 162 MB
> caom.temporalentity | 86 MB
> caom.plane_psi2 | 86 MB
> caom.spectralentity | 73 MB
> caom.metric | 70 MB
> caom.plane_energy_i1 | 67 MB
> caom.plane_time_i1 | 58 MB
> caom.plane_position_i2 | 48 MB
> caom.polarizationentity | 33 MB
> caom.simpleobservation_i2 | 25 MB
> caom.plane_psi1 | 23 MB
> caom.metric_i2 | 18 MB
> caom.metric_i1 | 18 MB
> caom.plane_i1 | 15 MB
> caom.plane_position_i3 | 15 MB
> caom.plane_polar_i1 | 15 MB
> caom.plane_time_i2 | 15 MB
> caom.plane_energy_i2 | 15 MB
> caom.plane_i2 | 15 MB
> caom.simpleobservation_i1 | 12 MB
> caom.temporalentity_i1 | 9496 kB
> caom.spectralentity_i1 | 4384 kB
> caom.polarizationentity_i1 | 4368 kB
> caom.harvestskip | 2056 kB
> pg_catalog.pg_depend | 1008 kB
> pg_catalog.pg_proc | 880 kB
> caom.harveststate | 856 kB
> pg_catalog.pg_attribute | 648 kB
> caom.positionhole | 584 kB
> pg_catalog.pg_statistic | 576 kB
> caom.plane_phi2 | 496 kB
> caom.harvestskip_i1 | 480 kB
> pg_catalog.pg_proc_proname_args_nsp_index | 328 kB
> pg_catalog.pg_operator | 296 kB
> pg_catalog.pg_description | 280 kB
> pg_catalog.pg_depend_depender_index | 264 kB
> pg_catalog.pg_depend_reference_index | 264 kB
> pg_catalog.pg_rewrite | 256 kB
> pg_catalog.pg_attribute_relid_attnam_index | 240 kB
> pg_toast.pg_toast_2618 | 160 kB
> pg_catalog.pg_type | 144 kB
> pg_catalog.pg_class | 136 kB
> pg_catalog.pg_amop | 128 kB
> pg_catalog.pg_operator_oprname_l_r_n_index | 112 kB
> pg_catalog.pg_description_o_c_o_index | 96 kB
> pg_toast.pg_toast_2619 | 96 kB
> pg_catalog.pg_constraint | 80 kB
> pg_catalog.pg_conversion | 80 kB
> pg_catalog.pg_amproc | 72 kB
> pg_catalog.pg_attribute_relid_attnum_index | 72 kB
> pg_catalog.pg_proc_oid_index | 72 kB
> pg_catalog.pg_opclass | 72 kB
> pg_catalog.pg_trigger | 56 kB
> pg_catalog.pg_type_typname_nsp_index | 56 kB
> pg_catalog.pg_index | 56 kB
> information_schema.sql_features | 48 kB
> pg_catalog.pg_cast | 48 kB
> pg_catalog.pg_class_relname_nsp_index | 48 kB
> pg_catalog.pg_database | 48 kB
> pg_catalog.pg_authid | 48 kB
> pg_catalog.pg_ts_config_map | 48 kB
> pg_catalog.pg_opfamily | 48 kB
> pg_catalog.pg_language | 40 kB
> pg_catalog.pg_shdepend | 40 kB
> pg_catalog.pg_ts_parser | 40 kB
> pg_catalog.pg_ts_template | 40 kB
> pg_catalog.pg_auth_members | 40 kB
> pg_catalog.pg_ts_config | 40 kB
> pg_catalog.pg_ts_dict | 40 kB
> pg_catalog.pg_operator_oid_index | 40 kB
> pg_catalog.pg_tablespace | 40 kB
> pg_catalog.pg_namespace | 40 kB
> pg_catalog.pg_am | 40 kB
> pg_toast.pg_toast_45054 | 32 kB
> pg_catalog.pg_conversion_name_nsp_index | 32 kB
> pg_catalog.pg_shdescription | 32 kB
> tap_schema.tables | 32 kB
> pg_catalog.pg_statistic_relid_att_index | 32 kB
> pg_catalog.pg_amop_fam_strat_index | 32 kB
> tap_schema.schemas | 32 kB
> pg_catalog.pg_amproc_fam_proc_index | 32 kB
> pg_catalog.pg_ts_config_map_index | 32 kB
> tap_schema.columns | 32 kB
> caom.plane_phi1 | 32 kB
> pg_catalog.pg_amop_oid_index | 32 kB
> pg_catalog.pg_amop_opr_fam_index | 32 kB
> pg_catalog.pg_opclass_am_name_nsp_index | 32 kB
> caom.deletedplane | 24 kB
> pg_catalog.pg_pltemplate | 24 kB
> pg_catalog.pg_attrdef | 24 kB
> tap_schema.keys | 24 kB
> caom.deletedspatialentity | 24 kB
> caom.deletedpolarizationentity | 24 kB
> caom.compositeobservation | 24 kB
> caom.deletedtemporalentity | 24 kB
> caom.deletedartifact | 24 kB
> caom.deletedspectralentity | 24 kB
> pg_catalog.pg_aggregate | 24 kB
> information_schema.sql_implementation_info | 16 kB
> information_schema.sql_languages | 16 kB
> pg_catalog.pg_enum | 16 kB
> pg_catalog.pg_ts_config_oid_index | 16 kB
> pg_catalog.pg_trigger_oid_index | 16 kB
> tap_schema.schemas_pkey | 16 kB
> pg_catalog.pg_constraint_conrelid_index | 16 kB
> pg_catalog.pg_conversion_default_index | 16 kB
> pg_catalog.pg_namespace_oid_index | 16 kB
> pg_catalog.pg_namespace_nspname_index | 16 kB
> pg_catalog.pg_cast_source_target_index | 16 kB
> pg_catalog.pg_constraint_contypid_index | 16 kB
> information_schema.sql_packages | 16 kB
> pg_catalog.pg_aggregate_fnoid_index | 16 kB
> pg_catalog.pg_rewrite_oid_index | 16 kB
> pg_toast.pg_toast_2618_index | 16 kB
> pg_catalog.pg_trigger_tgconstrname_index | 16 kB
> pg_catalog.pg_trigger_tgrelid_tgname_index | 16 kB
> information_schema.sql_parts | 16 kB
> pg_catalog.pg_authid_rolname_index | 16 kB
> pg_catalog.pg_authid_oid_index | 16 kB
> pg_catalog.pg_auth_members_role_member_index | 16 kB
> pg_catalog.pg_auth_members_member_role_index | 16 kB
> information_schema.sql_sizing | 16 kB
> tap_schema.keys_pkey | 16 kB
> tap_schema.columns_pkey | 16 kB
> caom.deletedplane_i1 | 16 kB
> pg_catalog.pg_database_datname_index | 16 kB
> pg_catalog.pg_database_oid_index | 16 kB
> caom.deletedpolarizationentity_i1 | 16 kB
> pg_catalog.pg_shdescription_o_c_index | 16 kB
> pg_catalog.pg_language_name_index | 16 kB
> pg_catalog.pg_language_oid_index | 16 kB
> pg_catalog.pg_index_indrelid_index | 16 kB
> caom.timesample | 16 kB
> pg_catalog.pg_am_name_index | 16 kB
> pg_toast.pg_toast_2619_index | 16 kB
> pg_catalog.pg_constraint_conname_nsp_index | 16 kB
> pg_catalog.pg_index_indexrelid_index | 16 kB
> pg_catalog.pg_am_oid_index | 16 kB
> pg_catalog.pg_pltemplate_name_index | 16 kB
> pg_catalog.pg_shdepend_depender_index | 16 kB
> pg_catalog.pg_shdepend_reference_index | 16 kB
> pg_catalog.pg_tablespace_oid_index | 16 kB
> pg_catalog.pg_tablespace_spcname_index | 16 kB
> pg_toast.pg_toast_45054_index | 16 kB
> caom.deletedtemporalentity_i1 | 16 kB
> pg_catalog.pg_ts_dict_oid_index | 16 kB
> caom.deletedspectralentity_i1 | 16 kB
> caom.deletedspatialentity_i1 | 16 kB
> caom.deletedartifact_i1 | 16 kB
> pg_catalog.pg_constraint_oid_index | 16 kB
> pg_catalog.pg_opfamily_am_name_nsp_index | 16 kB
> pg_catalog.pg_opfamily_oid_index | 16 kB
> pg_catalog.pg_opclass_oid_index | 16 kB
> pg_catalog.pg_amproc_oid_index | 16 kB
> pg_catalog.pg_class_oid_index | 16 kB
> pg_catalog.pg_rewrite_rel_rulename_index | 16 kB
> pg_catalog.pg_ts_config_cfgname_index | 16 kB
> tap_schema.tables_pkey | 16 kB
>
>
> Thank you
> Isabella
>
>
> Hi All,
>
> Please, see more info my env: PG 8.3.6 on RHE5-64bits.
>
> 1. there are more than one schemas, but the size of the tables is close to
> 30-40kB, see some samples
> schemaname | tablename | size_pretty | total_size_pretty
> ------------+-------------+-------------+-------------------
> tap_schema | tables | 8192 bytes | 32 kB
> tap_schema | columns | 8192 bytes | 32 kB
> tap_schema | schemas | 8192 bytes | 32 kB
> tap_schema | keys | 8192 bytes | 24 kB
> tap_schema | key_columns | 8192 bytes | 8192 bytes
>
> 2. There are no BLOB's data type in db at this time :
> cvodb=# select * from pg_largeobject;
> loid | pageno | data
>
> ------+--------+------
> (0 rows)
>
>
> 3. As Tom suggested , I excluded the table space restriction and
> changed to pg_total_relation_size my original SQL :
>
>
> SELECT 'the table size without table space restrictions';
> SELECT nspname || '.' || relname AS
> "relation",pg_size_pretty(pg_total_relation_size(nspname || '.' ||
> relname)) AS "s
> ize"
> FROM pg_class C
> LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
> ORDER BY pg_relation_size(nspname || '.' || relname) DESC
> LIMIT 20;
> the table size without table space restrictions
> (1 row)
>
> relation | size
> ---------------------------+---------
> caom.spatialentity | 3216 MB
> caom.artifact | 2150 MB
> caom.plane | 677 MB
> caom.artifact_i1 | 171 MB
> caom.simpleobservation | 202 MB
> caom.spatialentity_i1 | 162 MB
> caom.positionsample | 219 MB
> caom.plane_psi2 | 86 MB
> caom.temporalentity | 86 MB
> caom.spectralentity | 73 MB
> caom.plane_energy_i1 | 67 MB
> caom.plane_time_i1 | 58 MB
> caom.plane_position_i2 | 48 MB
> caom.metric | 70 MB
> caom.polarizationentity | 33 MB
> caom.simpleobservation_i2 | 25 MB
> caom.plane_psi1 | 23 MB
> caom.metric_i2 | 18 MB
> caom.metric_i1 | 18 MB
> caom.plane_position_i3 | 15 MB
> (20 rows)
>
>
> 4. Where are the rest of 5,5 GB been used ? How can I get the system
> catalog correct size ?
>
>
>
> Thank you,
> Isabella
>
> Tom Lane wrote:
>>
>> Isabella Ghiurea writes:
>> > I'm trying to understand why there are GB's difference when checking
>> > for db size using pg_size_pretty() and querying for tables + indexes
>> > size. .
>>
>> You are not counting everything --- the total DB size is clearly 12GB,
>> so the question is where are the other 5.5GB? Your first query shows
>> that schema caom accounts for 6+GB, but the second one does not prove
>> that schema caom contains all the big hogs. My guesses are:
>>
>> 1. Toast tables for tables that aren't in caom --- you used
>> pg_relation_size not pg_total_relation_size, and excluded toast
>> tables, so you are missing those.
>>
>> 2. pg_largeobject ... got any large objects?
>>
>> 3. Bloat in other system catalogs. 5GB of catalog bloat would be
>> pretty awful, but maybe that's what it is.
>>
>> Try that last query without the namespace restrictions.
>>
>
>
>>
>> regards, tom lane
>
> Hi Pg Admin list.
> I'm trying to understand why there are GB's difference when checking
> for db size using pg_size_pretty() and querying for tables + indexes
> size. .
> The sum of tables +index sizes is showing as aprox 6.5GB and
> pg_size_pretty(dbname) is coming as 12GB, this are the results after a
> full vacuum and reindexdb, also the sum of OS db files size is ~ 6.5GB.
> Any tips what I'm missing : are some "unallocated" db pages or anything
> else ?
>
> select pg_size_pretty(pg_database_size('db1'));
> pg_size_pretty
> ----------------
> 12 GB
> (1 row)
> *** Check for tables size :
> SELECT schemaname, tablename,
> pg_size_pretty(size) AS size_pretty,
> pg_size_pretty(total_size) AS total_size_pretty
> FROM (SELECT *,
> pg_relation_size(schemaname||'.'||tablename) AS
> size,pg_total_relation_size(schemaname||'.'||tablename) AS
> total_size FROM pg_tables) AS TABLES where schemaname='caom'
> ORDER BY total_size DESC;
> schemaname | tablename | size_pretty | total_size_pretty
> ------------+-----------------------------+-------------+--- ----------------
> caom | spatialentity | 3053 MB | 3216 MB
> caom | artifact | 1979 MB | 2150 MB
> caom | plane | 413 MB | 677 MB
> caom | positionsample | 110 MB | 219 MB
> caom | simpleobservation | 165 MB | 202 MB
> caom | temporalentity | 77 MB | 86 MB
> caom | spectralentity | 68 MB | 73 MB
> caom | metric | 33 MB | 70 MB
> caom | polarizationentity | 29 MB | 33 MB
> caom | harvestskip | 1576 kB | 2056 kB
> caom | harveststate | 840 kB | 856 kB
> caom | positionhole | 48 kB | 584 kB
>
>
>
>
> *** OR : check for the biggest tables+index size:
> SELECT ' Top 20 biggest tables and indexes'
> ;
> SELECT nspname || '.' || relname AS "relation",
> pg_size_pretty(pg_relation_size(nspname || '.' || relname)) AS "size"
> FROM pg_class C
> LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
> WHERE nspname NOT IN ('pg_catalog', 'information_schema')
> AND nspname !~ '^pg_toast'
> AND pg_relation_size(nspname || '.' || relname)>0
> ORDER BY pg_relation_size(nspname || '.' || relname) DESC
> LIMIT 20;
> (1 row)
>
> relation | size
> ---------------------------+---------
> caom.spatialentity | 3053 MB
> caom.artifact | 1979 MB
> caom.plane | 413 MB
> caom.artifact_i1 | 171 MB
> caom.simpleobservation | 165 MB
> caom.spatialentity_i1 | 162 MB
> caom.positionsample | 110 MB
> caom.plane_psi2 | 86 MB
> caom.temporalentity | 77 MB
> caom.spectralentity | 68 MB
> caom.plane_energy_i1 | 67 MB
> caom.plane_time_i1 | 58 MB
> caom.plane_position_i2 | 48 MB
> caom.metric | 33 MB
> caom.polarizationentity | 29 MB
> caom.simpleobservation_i2 | 25 MB
> caom.plane_psi1 | 23 MB
> caom.metric_i2 | 18 MB
>
> Thank you
> Isabella
>
>
>

--
View this message in context: http://www.nabble.com/Re%3A--ADMIN--db-size-and-tables-size- difference-tp25531211p25531314.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: db size and tables size difference

Isabella Ghiurea writes:
> The issue may be with pg_size_pretty() results, I don't have details
> knowledge of this function.

I doubt it, that's a pretty simple function ... but if you don't trust
it, just remove the pg_size_pretty call and look directly at the output
of the size functions.

> select pg_size_pretty(pg_database_size('db1'));

Just to double check here ... you're sure you're naming the correct
database in this call?

regards, tom lane

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

db size and tables size difference

Tom Lane wrote:
>
> Isabella Ghiurea writes:
> > The issue may be with pg_size_pretty() results, I don't have details
> > knowledge of this function.
>
> I doubt it, that's a pretty simple function ... but if you don't trust
> it, just remove the pg_size_pretty call and look directly at the output
> of the size functions.
>
Hi Tom,
pg_tablespace_size() function shows same size as tables size SQL
statements close to 6.5 GB , here is output for the bigest tablespace
select pg_tablespace_size('caom_data');
pg_tablespace_size
--------------------
6218108932
select pg_tablespace_size('caom_index');
pg_tablespace_size
--------------------
837201924
select pg_tablespace_size('tap_data');
pg_tablespace_size
--------------------
65689

select pg_database_size('cvodb');
pg_database_size
------------------
13279260268
How can I get more details of how pg_database_size() is been executed ?
>
>
> > select pg_size_pretty(pg_database_size('db1'));
>
> Just to double check here ... you're sure you're naming the correct
> database in this call?
>
yes
Thank you
Isabella
>
> regards, tom lane
>


--
-----------------------------------------------------------
Isabella A. Ghiurea

Isabella.Ghiurea@nrc-cnrc.gc.ca
Canadian Astronomy Data Centre | http://www1.cadc-ccda.hia-iha.nrc-cnrc.gc.ca/
National Research Council of Canada, Herzberg Institute of Astrophysics
5071 West Saanich Road, Victoria BC V9E 2E7, Canada
Phone: 250 363-3446 fax: 250 363-0045


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Slony-I - Update trigger on a replicated table

Anyone know if it is possible to create an update trigger on the destinatio=
n side of a replicated table in Slony-I and have the replication itself cau=
se the trigger to fire? I was able to add the trigger but when run the test=
transaction through, it does not seem to be firing. It works on the non-r=
eplicated version of the solution.

Running postgres 8.4.1 and Slony-I 2.0.3


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Slony-I - Update trigger on a replicated table

Nevermind ... I RTFM ...

echo "alter table mytable ENABLE REPLICA TRIGGER check_lockout;" | psql mydb

-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql=
..org] On Behalf Of Plugge, Joe R.
Sent: Thursday, May 13, 2010 4:02 PM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] Slony-I - Update trigger on a replicated table

Anyone know if it is possible to create an update trigger on the destinatio=
n side of a replicated table in Slony-I and have the replication itself cau=
se the trigger to fire? I was able to add the trigger but when run the test=
transaction through, it does not seem to be firing. It works on the non-r=
eplicated version of the solution.

Running postgres 8.4.1 and Slony-I 2.0.3


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Slony-I - Update trigger on a replicated table

--001636137b080fcc92048680b44d
Content-Type: text/plain; charset=ISO-8859-1

On Thu, May 13, 2010 at 5:11 PM, Plugge, Joe R. wrote:

> Nevermind ... I RTFM ...
>
> echo "alter table mytable ENABLE REPLICA TRIGGER check_lockout;" | psql
> mydb
>

This is not good idea. Always run DDL script using Slony execute script.

http://www.slony.info/documentation/ddlchanges.html



>
> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org [mailto:
> pgsql-admin-owner@postgresql.org] On Behalf Of Plugge, Joe R.
> Sent: Thursday, May 13, 2010 4:02 PM
> To: pgsql-admin@postgresql.org
> Subject: [ADMIN] Slony-I - Update trigger on a replicated table
>
> Anyone know if it is possible to create an update trigger on the
> destination side of a replicated table in Slony-I and have the replication
> itself cause the trigger to fire? I was able to add the trigger but when run
> the test transaction through, it does not seem to be firing. It works on
> the non-replicated version of the solution.
>
> Running postgres 8.4.1 and Slony-I 2.0.3
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>

--001636137b080fcc92048680b44d
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Slony - I question

--_000_BD69807DAE0CE44CA00A8338D0FDD08302E183E9BDoma00cexmbx 03_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

I am using slony1-2.0.3 and needed to make some wholesale changes, so like =
other times I stopped all slon processes on my master box as well as on the=
2 slave machines. I then dropped the slony schema on all three, completel=
y removing it, made my changes. I then ran my slonik script at the master,=
started up all of the slon processes and ran my subscribe script on both s=
laves. My problem, is that only one of the slaves got the copy and synched=
, the other one just hangs at this point:



2010-06-23 03:43:18 EDT CONFIG storeListen: li_origin=3D1 li_receiver=3D3 l=
i_provider=3D1

2010-06-23 03:43:18 EDT CONFIG storeListen: li_origin=3D2 li_receiver=3D3 l=
i_provider=3D1

2010-06-23 03:43:18 EDT CONFIG storeListen: li_origin=3D1 li_receiver=3D3 l=
i_provider=3D1

2010-06-23 03:43:18 EDT CONFIG storeListen: li_origin=3D2 li_receiver=3D3 l=
i_provider=3D1

2010-06-23 03:43:18 EDT CONFIG storeListen: li_origin=3D1 li_receiver=3D3 l=
i_provider=3D1

2010-06-23 03:43:18 EDT CONFIG storeListen: li_origin=3D2 li_receiver=3D3 l=
i_provider=3D1

2010-06-23 03:43:19 EDT CONFIG remoteWorkerThread_1: update provider config=
uration

2010-06-23 03:44:01 EDT CONFIG storeSubscribe: sub_set=3D1 sub_provider=3D1=
sub_forward=3D'f'

NOTICE: subscribe set: omit_copy=3Df

2010-06-23 03:44:02 EDT CONFIG storeListen: li_origin=3D1 li_receiver=3D3 l=
i_provider=3D1

2010-06-23 03:44:02 EDT CONFIG storeListen: li_origin=3D2 li_receiver=3D3 l=
i_provider=3D1

2010-06-23 03:44:02 EDT INFO copy_set 1 - omit=3Df - bool=3D0

2010-06-23 03:44:02 EDT INFO omit is FALSE

2010-06-23 03:44:02 EDT CONFIG version for "dbname=3Dholly host=3Dlinux2276=
port=3D5432 user=3Dslony" is 80401

2010-06-23 03:44:02 EDT CONFIG remoteWorkerThread_1: connected to provider =
DB

2010-06-23 03:44:02 EDT CONFIG remoteWorkerThread_1: prepare to copy table =
"public"."hlm_owners"



I query the pg_stat_activity table and see that the table in question has b=
een locked ....



I am using postgres 8.4.1. This worked many times before, so I am wonderin=
g if I am running into some new condition.

Just wondering what else to possible check for?



joe

--_000_BD69807DAE0CE44CA00A8338D0FDD08302E183E9BDoma00cexmbx 03_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:x=3D"urn:schemas-microsoft-com:office:excel" xmlns:p=3D"urn:schemas-m=
icrosoft-com:office:powerpoint" xmlns:a=3D"urn:schemas-microsoft-com:office=
:access" xmlns:dt=3D"uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:s=3D"=
uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:rs=3D"urn:schemas-microsof=
t-com:rowset" xmlns:z=3D"#RowsetSchema" xmlns:b=3D"urn:schemas-microsoft-co=
m:office:publisher" xmlns:ss=3D"urn:schemas-microsoft-com:office:spreadshee=
t" xmlns:c=3D"urn:schemas-microsoft-com:office:component:spread sheet" xmlns=
:odc=3D"urn:schemas-microsoft-com:office:odc" xmlns:oa=3D"urn:schemas-micro=
soft-com:office:activation" xmlns:html=3D"http://www.w3.org/TR/REC-html40" =
xmlns:q=3D"http://schemas.xmlsoap.org/soap/envelope/" xmlns:rtc=3D"http://m=
icrosoft.com/officenet/conferencing" xmlns:D=3D"DAV:" xmlns:Repl=3D"http://=
schemas.microsoft.com/repl/" xmlns:mt=3D"http://schemas.microsoft.com/share=
point/soap/meetings/" xmlns:x2=3D"http://schemas.microsoft.com/office/excel=
/2003/xml" xmlns:ppda=3D"http://www.passport.com/NameSpace.xsd" xmlns:ois=
=3D"http://schemas.microsoft.com/sharepoint/soap/ois/" xmlns:dir=3D"http://=
schemas.microsoft.com/sharepoint/soap/directory/" xmlns:ds=3D"http://www.w3=
..org/2000/09/xmldsig#" xmlns:dsp=3D"http://schemas.microsoft.com/sharepoint=
/dsp" xmlns:udc=3D"http://schemas.microsoft.com/data/udc" xmlns:xsd=3D"http=
://www.w3.org/2001/XMLSchema" xmlns:sub=3D"http://schemas.microsoft.com/sha=
repoint/soap/2002/1/alerts/" xmlns:ec=3D"http://www.w3.org/2001/04/xmlenc#"=
xmlns:sp=3D"http://schemas.microsoft.com/sharepoint/" xmlns:sps=3D"http://=
schemas.microsoft.com/sharepoint/soap/" xmlns:xsi=3D"http://www.w3.org/2001=
/XMLSchema-instance" xmlns:udcs=3D"http://schemas.microsoft.com/data/udc/so=
ap" xmlns:udcxf=3D"http://schemas.microsoft.com/data/udc/xmlfile " xmlns:udc=
p2p=3D"http://schemas.microsoft.com/data/udc/parttopart" xmlns:wf=3D"http:/=
/schemas.microsoft.com/sharepoint/soap/workflow/" xmlns:dsss=3D"http://sche=
mas.microsoft.com/office/2006/digsig-setup" xmlns:dssi=3D"http://schemas.mi=
crosoft.com/office/2006/digsig" xmlns:mdssi=3D"http://schemas.openxmlformat=
s.org/package/2006/digital-signature" xmlns:mver=3D"http://schemas.openxmlf=
ormats.org/markup-compatibility/2006" xmlns:m=3D"http://schemas.microsoft.c=
om/office/2004/12/omml" xmlns:mrels=3D"http://schemas.openxmlformats.org/pa=
ckage/2006/relationships" xmlns:spwp=3D"http://microsoft.com/sharepoint/web=
partpages" xmlns:ex12t=3D"http://schemas.microsoft.com/exchange/service s/20=
06/types" xmlns:ex12m=3D"http://schemas.microsoft.com/exchange/service s/200=
6/messages" xmlns:pptsl=3D"http://schemas.microsoft.com/sharepoint/soap/ Sli=
deLibrary/" xmlns:spsl=3D"http://microsoft.com/webservices/SharePointPor tal=
Server/PublishedLinksService" xmlns:Z=3D"urn:schemas-microsoft-com:" xmlns:=
st=3D"
" xmlns=3D"http://www.w3.org/TR/REC-html40">

Re: Slony - I question

--_000_BD69807DAE0CE44CA00A8338D0FDD08302E183EADBoma00cexmbx 03_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Found the culprit, the app had thrown locks on the first table in the repli=
cate set and it was waiting for those locks to be released.

From: Plugge, Joe R.
Sent: Wednesday, June 23, 2010 12:23 PM
To: pgsql-admin@postgresql.org
Subject: Slony - I question


I am using slony1-2.0.3 and needed to make some wholesale changes, so like =
other times I stopped all slon processes on my master box as well as on the=
2 slave machines. I then dropped the slony schema on all three, completel=
y removing it, made my changes. I then ran my slonik script at the master,=
started up all of the slon processes and ran my subscribe script on both s=
laves. My problem, is that only one of the slaves got the copy and synched=
, the other one just hangs at this point:



2010-06-23 03:43:18 EDT CONFIG storeListen: li_origin=3D1 li_receiver=3D3 l=
i_provider=3D1

2010-06-23 03:43:18 EDT CONFIG storeListen: li_origin=3D2 li_receiver=3D3 l=
i_provider=3D1

2010-06-23 03:43:18 EDT CONFIG storeListen: li_origin=3D1 li_receiver=3D3 l=
i_provider=3D1

2010-06-23 03:43:18 EDT CONFIG storeListen: li_origin=3D2 li_receiver=3D3 l=
i_provider=3D1

2010-06-23 03:43:18 EDT CONFIG storeListen: li_origin=3D1 li_receiver=3D3 l=
i_provider=3D1

2010-06-23 03:43:18 EDT CONFIG storeListen: li_origin=3D2 li_receiver=3D3 l=
i_provider=3D1

2010-06-23 03:43:19 EDT CONFIG remoteWorkerThread_1: update provider config=
uration

2010-06-23 03:44:01 EDT CONFIG storeSubscribe: sub_set=3D1 sub_provider=3D1=
sub_forward=3D'f'

NOTICE: subscribe set: omit_copy=3Df

2010-06-23 03:44:02 EDT CONFIG storeListen: li_origin=3D1 li_receiver=3D3 l=
i_provider=3D1

2010-06-23 03:44:02 EDT CONFIG storeListen: li_origin=3D2 li_receiver=3D3 l=
i_provider=3D1

2010-06-23 03:44:02 EDT INFO copy_set 1 - omit=3Df - bool=3D0

2010-06-23 03:44:02 EDT INFO omit is FALSE

2010-06-23 03:44:02 EDT CONFIG version for "dbname=3Dholly host=3Dlinux2276=
port=3D5432 user=3Dslony" is 80401

2010-06-23 03:44:02 EDT CONFIG remoteWorkerThread_1: connected to provider =
DB

2010-06-23 03:44:02 EDT CONFIG remoteWorkerThread_1: prepare to copy table =
"public"."hlm_owners"



I query the pg_stat_activity table and see that the table in question has b=
een locked ....



I am using postgres 8.4.1. This worked many times before, so I am wonderin=
g if I am running into some new condition.

Just wondering what else to possible check for?



joe

--_000_BD69807DAE0CE44CA00A8338D0FDD08302E183EADBoma00cexmbx 03_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:x=3D"urn:schemas-microsoft-com:office:excel" xmlns:p=3D"urn:schemas-m=
icrosoft-com:office:powerpoint" xmlns:a=3D"urn:schemas-microsoft-com:office=
:access" xmlns:dt=3D"uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:s=3D"=
uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:rs=3D"urn:schemas-microsof=
t-com:rowset" xmlns:z=3D"#RowsetSchema" xmlns:b=3D"urn:schemas-microsoft-co=
m:office:publisher" xmlns:ss=3D"urn:schemas-microsoft-com:office:spreadshee=
t" xmlns:c=3D"urn:schemas-microsoft-com:office:component:spread sheet" xmlns=
:odc=3D"urn:schemas-microsoft-com:office:odc" xmlns:oa=3D"urn:schemas-micro=
soft-com:office:activation" xmlns:html=3D"http://www.w3.org/TR/REC-html40" =
xmlns:q=3D"http://schemas.xmlsoap.org/soap/envelope/" xmlns:rtc=3D"http://m=
icrosoft.com/officenet/conferencing" xmlns:D=3D"DAV:" xmlns:Repl=3D"http://=
schemas.microsoft.com/repl/" xmlns:mt=3D"http://schemas.microsoft.com/share=
point/soap/meetings/" xmlns:x2=3D"http://schemas.microsoft.com/office/excel=
/2003/xml" xmlns:ppda=3D"http://www.passport.com/NameSpace.xsd" xmlns:ois=
=3D"http://schemas.microsoft.com/sharepoint/soap/ois/" xmlns:dir=3D"http://=
schemas.microsoft.com/sharepoint/soap/directory/" xmlns:ds=3D"http://www.w3=
..org/2000/09/xmldsig#" xmlns:dsp=3D"http://schemas.microsoft.com/sharepoint=
/dsp" xmlns:udc=3D"http://schemas.microsoft.com/data/udc" xmlns:xsd=3D"http=
://www.w3.org/2001/XMLSchema" xmlns:sub=3D"http://schemas.microsoft.com/sha=
repoint/soap/2002/1/alerts/" xmlns:ec=3D"http://www.w3.org/2001/04/xmlenc#"=
xmlns:sp=3D"http://schemas.microsoft.com/sharepoint/" xmlns:sps=3D"http://=
schemas.microsoft.com/sharepoint/soap/" xmlns:xsi=3D"http://www.w3.org/2001=
/XMLSchema-instance" xmlns:udcs=3D"http://schemas.microsoft.com/data/udc/so=
ap" xmlns:udcxf=3D"http://schemas.microsoft.com/data/udc/xmlfile " xmlns:udc=
p2p=3D"http://schemas.microsoft.com/data/udc/parttopart" xmlns:wf=3D"http:/=
/schemas.microsoft.com/sharepoint/soap/workflow/" xmlns:dsss=3D"http://sche=
mas.microsoft.com/office/2006/digsig-setup" xmlns:dssi=3D"http://schemas.mi=
crosoft.com/office/2006/digsig" xmlns:mdssi=3D"http://schemas.openxmlformat=
s.org/package/2006/digital-signature" xmlns:mver=3D"http://schemas.openxmlf=
ormats.org/markup-compatibility/2006" xmlns:m=3D"http://schemas.microsoft.c=
om/office/2004/12/omml" xmlns:mrels=3D"http://schemas.openxmlformats.org/pa=
ckage/2006/relationships" xmlns:spwp=3D"http://microsoft.com/sharepoint/web=
partpages" xmlns:ex12t=3D"http://schemas.microsoft.com/exchange/service s/20=
06/types" xmlns:ex12m=3D"http://schemas.microsoft.com/exchange/service s/200=
6/messages" xmlns:pptsl=3D"http://schemas.microsoft.com/sharepoint/soap/ Sli=
deLibrary/" xmlns:spsl=3D"http://microsoft.com/webservices/SharePointPor tal=
Server/PublishedLinksService" xmlns:Z=3D"urn:schemas-microsoft-com:" xmlns:=
st=3D"
" xmlns=3D"http://www.w3.org/TR/REC-html40">


>